#用于加载数据
from sqlalchemy import create_engine
import pandas as pd
import datetime as dt
import os

class DataLoaderAndSaver:
    #初始化数据库链接
    def __init__(self,
                 host='119.45.208.182',
                 username='chandliu',
                 passwd='chandliu275288',
                 dbname='stocks',
                 # host='localhost',
                 # username='root',
                 # passwd='3834uflp',
                 # dbname='mystocks',
                 port=3306,
                 daysBefore1=0,
                 daysBefore2=1,
                 yearsBefore=3,
                 reload=False):
        self.host=host
        self.username=username
        self.passwd=passwd
        self.port=port
        self.dbname=dbname
        url = 'mysql+pymysql://%s:%s@%s:%d/%s?charset=utf8' % (self.username,self.passwd,self.host, self.port,self.dbname)
        print(url)
        self.engine = create_engine(url, echo=False)
        self.reload=reload
        self.todayDate = (dt.datetime.now() + dt.timedelta(days=daysBefore1)).strftime('%Y-%m-%d')
        self.startDate = (dt.datetime.now() + dt.timedelta(days=-244 * yearsBefore)).strftime('%Y-%m-%d')
        self.endDate = (dt.datetime.now() + dt.timedelta(days=daysBefore1)).strftime('%Y-%m-%d')
        self.endDate2 = (dt.datetime.now() + dt.timedelta(days=daysBefore2)).strftime('%Y-%m-%d')
        self.startTradeDate = (dt.datetime.now() + dt.timedelta(days=-180)).strftime('%Y-%m-%d')
        print(yearsBefore, self.startDate, self.todayDate, self.endDate, self.endDate2,self.startTradeDate )
        # print("select * from dailyprices_20211114 where date>='%s' and date<='%s'"%(self.startDate,self.todayDate))
        # print("select * from `dailyprices_fix_%s`"%(self.todayDate))
        # self.dailyprices = self.__loadDailyPrices()  # 加载股价数据,日行情数据
        # print(self.dailyprices.shape)
        # self.dailyprices.sort_index()
        self.allstocks=self.__loadAllStocks()#加载所有的股票名称基本数据
        self.alletfs=self.__loadAllETFS()#加载所有的ETF
        self.allindexs=self.__loadAllIndex()#加载所有的指数

    #下载日数据
    def __loadData(self,sql):
        data=pd.read_sql(sql,con=self.engine)
        return data

    def __loadAllStocks(self):
        sql='select * from allstocks'
        print(sql)
        allstocks=self.__loadData(sql)
        allstocks.index = allstocks['code']
        allstocks=allstocks.drop(['code','type'],axis=1)
        allstocks=allstocks[~allstocks['display_name'].str.contains('st|ST|退')]
        print(allstocks.head(10))
        return allstocks

    def __loadAllETFS(self):
        sql='select * from etfnames'
        print(sql)
        allstocks=self.__loadData(sql)
        allstocks.index = allstocks['code']
        allstocks=allstocks.drop(['code','type'],axis=1)
        allstocks=allstocks[~allstocks['display_name'].str.contains('st|ST|退')]
        print(allstocks.head(10))
        return allstocks

    def __loadAllIndex(self):
        sql='select * from indexnames'
        print(sql)
        allstocks=self.__loadData(sql)
        allstocks.index = allstocks['code']
        allstocks=allstocks.drop(['code','type'],axis=1)
        allstocks=allstocks[~allstocks['display_name'].str.contains('st|ST|退')]
        print(allstocks.head(10))
        return allstocks

    #加载股票的日交易数据
    def loadDailyPrices(self):
        fpath='F:/GitHub/StocksFeatureCalculation/localData/dailyprices/'+self.todayDate+'.csv'
        if os.path.exists(fpath) and not self.reload:
            dailyprices=pd.read_csv(fpath)
            print(dailyprices.columns)
            if 'Unnamed: 0' in dailyprices.columns:
                dailyprices=dailyprices.drop('Unnamed: 0',axis=1)
            dailyprices.index=dailyprices['date']
            print(dailyprices.shape)
            dailyprices=dailyprices.drop_duplicates(keep='last')
            print(dailyprices.shape)
            print('load stock daily prices from local')
        else:
            # sql="select * from 'dailyprices_fix_%s' where date>='%s' and date<='%s'"%(self.todayDate,self.startDate,self.todayDate)
            sql="select * from `dailyprices_fix_%s`"%(self.todayDate.replace('-','_'))
            print(sql)
            dailyprices = self.__loadData(sql)
            dailyprices.sort_index()
            print(dailyprices.shape)
            dailyprices = dailyprices.drop_duplicates(keep='last')
            print(dailyprices.shape)
            dailyprices=dailyprices.dropna()
            print(dailyprices.shape)
            dailyprices.to_csv(fpath,index=False)
            print(dailyprices[dailyprices['date']==self.endDate2].head(10))
            print('load stock daily prices from db')
        print(dailyprices.head(10))
        return dailyprices

    #加载ETF的数据
    def loadETFDailyPrices(self):
        fpath='F:/GitHub/StocksFeatureCalculation/localData/etfdailyprices/'+self.todayDate+'.csv'
        if os.path.exists(fpath) and not self.reload:
            dailyprices=pd.read_csv(fpath)
            print(dailyprices.columns)
            if 'Unnamed: 0' in dailyprices.columns:
                dailyprices=dailyprices.drop('Unnamed: 0',axis=1)
            dailyprices.index=dailyprices['date']
            print(dailyprices.shape)
            dailyprices=dailyprices.drop_duplicates(keep='last')
            print(dailyprices.shape)
            print('load etf daily prices from local')
        else:
            # sql="select * from 'dailyprices_fix_%s' where date>='%s' and date<='%s'"%(self.todayDate,self.startDate,self.todayDate)
            sql="select * from `etfdailyprices_%s`"%(self.todayDate.replace('-','_'))
            print(sql)
            dailyprices = self.__loadData(sql)
            dailyprices.sort_index()
            print(dailyprices.shape)
            dailyprices = dailyprices.drop_duplicates(keep='last')
            print(dailyprices.shape)
            dailyprices=dailyprices.dropna()
            print(dailyprices.shape)
            dailyprices.to_csv(fpath,index=False)
            print(dailyprices[dailyprices['date']==self.endDate2].head(10))
            print('load etf daily prices from db')
        print(dailyprices.head(10))
        return dailyprices

    # 加载ETF的数据
    def loadIndexDailyPrices(self):
        fpath = 'F:/GitHub/StocksFeatureCalculation/localData/indexdailyprices/' + self.todayDate + '.csv'
        if os.path.exists(fpath) and not self.reload:
            dailyprices = pd.read_csv(fpath)
            print(dailyprices.columns)
            if 'Unnamed: 0' in dailyprices.columns:
                dailyprices = dailyprices.drop('Unnamed: 0', axis=1)
            dailyprices.index = dailyprices['date']
            print(dailyprices.shape)
            dailyprices = dailyprices.drop_duplicates(keep='last')
            print(dailyprices.shape)
            print('load index daily prices from local')
        else:
            # sql="select * from 'dailyprices_fix_%s' where date>='%s' and date<='%s'"%(self.todayDate,self.startDate,self.todayDate)
            sql = "select * from `indexdailyprices_%s`" % (self.todayDate.replace('-', '_'))
            print(sql)
            dailyprices = self.__loadData(sql)
            dailyprices.sort_index()
            print(dailyprices.shape)
            dailyprices = dailyprices.drop_duplicates(keep='last')
            print(dailyprices.shape)
            dailyprices = dailyprices.dropna()
            print(dailyprices.shape)
            dailyprices.to_csv(fpath, index=False)
            print(dailyprices[dailyprices['date'] == self.endDate2].head(10))
            print('load index daily prices from db')
        print(dailyprices.head(10))
        return dailyprices

    #存储数据导数据表
    def saveData(self,tableName,data,if_exists = 'append'):
        data.to_sql(tableName, self.engine, index=False, if_exists=if_exists)

    def dropTable(self,tableName):
        sql='drop table '+tableName
        print(sql)
        pd.read_sql(sql,self.engine)
